﻿Imports System.Data.SqlClient
Public Class frmRDieselKms
    Dim Bitacora As New clsActividad
    Dim Usuario As New clsUsuarioPrincipal

    Private Sub frmRDieselKms_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ToolStrip1.Cursor = Cursors.Hand
        ToolStrip1.RenderMode = System.Windows.Forms.ToolStripRenderMode.System

        lstA.Items.Clear()
        lstR.Items.Clear()
        cargaAutobuses()
        CargaRutas()
        RadioButton1.Checked = True
        chColores.Checked = True

    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Me.Close()
    End Sub
    Sub CargaRutas()
        lstRuta.Items.Clear()

        Dim cnconn As New SqlConnection
        cnconn.ConnectionString = CitraConnection

        Dim strSql As String = ""
        strSql = "select Nombre from Rutas order by Nombre"

        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnconn
        cmdBuscar.CommandText = strSql

        Dim rdBuscar As SqlDataReader
        cnconn.Open()
        rdBuscar = cmdBuscar.ExecuteReader

        Do While rdBuscar.Read()
            Me.lstRuta.Items.Add(rdBuscar("Nombre").ToString.Trim)
        Loop
    End Sub
    Sub cargaAutobuses()
        lstAutobus.Items.Clear()

        Dim cnconn As New SqlConnection
        cnconn.ConnectionString = CitraConnection

        Dim strSql As String = ""
        strSql = "select NoEconomico from Autobuses where Estatus=1 order by NoEconomico"

        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnconn
        cmdBuscar.CommandText = strSql

        Dim rdBuscar As SqlDataReader
        cnconn.Open()
        rdBuscar = cmdBuscar.ExecuteReader
        Dim primero As Boolean
        primero = True

        Do While rdBuscar.Read()

            Me.lstAutobus.Items.Add(rdBuscar("NoEconomico").ToString.Trim)
        Loop

    End Sub

    Private Sub RadioButton1_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadioButton1.CheckedChanged
        lstA.Enabled = False
        lstAutobus.Enabled = False
        lstR.Enabled = False
        lstRuta.Enabled = False
    End Sub

    Private Sub RadioButton2_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadioButton2.CheckedChanged
        lstA.Enabled = False
        lstAutobus.Enabled = False
        lstR.Enabled = True
        lstRuta.Enabled = True
    End Sub

    Private Sub RadioButton3_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadioButton3.CheckedChanged
        lstA.Enabled = True
        lstAutobus.Enabled = True
        lstR.Enabled = False
        lstRuta.Enabled = False
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Dim aux As String = ""
        aux = lstRuta.Text.ToString.Trim
        If aux <> "" Then
            lstR.Items.Add(lstRuta.SelectedItem)
            lstRuta.Items.Remove(lstRuta.SelectedItem)
        End If

    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Dim aux As String = ""
        aux = lstR.Text.ToString.Trim
        If aux <> "" Then
            lstRuta.Items.Add(lstR.SelectedItem)
            lstR.Items.Remove(lstR.SelectedItem)
        End If
    End Sub

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
        Dim aux As String = ""
        aux = lstAutobus.Text.ToString.Trim
        If aux <> "" Then
            lstA.Items.Add(lstAutobus.SelectedItem)
            lstAutobus.Items.Remove(lstAutobus.SelectedItem)
        End If
    End Sub

    Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
        Dim aux As String = ""
        aux = lstA.Text.ToString.Trim
        If aux <> "" Then
            lstAutobus.Items.Add(lstA.SelectedItem)
            lstA.Items.Remove(lstA.SelectedItem)
        End If
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        'Registra actividad
        Bitacora.RegistraActividad("Imprimió el reporte de rendimientos de diesel del dia " & datFecha1.Text & " al " & datFecha2.Text)
        If RadioButton1.Checked = True Then
            todos()
        ElseIf RadioButton2.Checked = True Then
            ruta()
        Else
            autobus()
        End If
        'Dim t As Integer = lstR.Items.Count
        'MsgBox(t)
        'Dim i As Integer
        'For i = 0 To lstR.Items.Count - 1
        'MsgBox(lstR.Items(i).ToString)
        'Next
    End Sub
    Sub todos()
        Dim xl As Object
        Dim wb As Object
        Dim i As Integer
        xl = CreateObject("Excel.Application")
        xl.Visible = True
        xl.Workbooks.Add()
        wb = xl.activeworkbook
        Dim rng As Integer
        rng = 5

        wb.SHEETS(1).cells(1, 1).value = "Imprimiendo..."
        wb.SHEETS(1).columns(1).columnwidth = 15

        Dim strFecha1 As String = datFecha1.Value.ToString("MM/dd/yyyy")
        Dim strFecha2 As String = datFecha2.Value.ToString("MM/dd/yyyy")
        '--------
        Dim cnconn As New SqlConnection
        cnconn.ConnectionString = CitraConnection
        Dim strSql As String = ""
        cnconn.Open()
        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnconn
        Dim cmdAsignar As New SqlCommand
        cmdAsignar.Connection = cnconn

        strSql = "SELECT A.IdRuta,R.Nombre,D.IdAutobus,D.NoEconomico,Litros,KilometrosTotales,RendimientoStandart FROM Diesel D INNER JOIN Autobuses A ON D.IdAutobus=A.IdAutobus INNER JOIN Rutas R ON R.IdRuta=A.IdRuta WHERE CONVERT(VARCHAR,Fecha,101)>='" & strFecha1 & "' AND CONVERT(VARCHAR,Fecha,101)<='" & strFecha2 & "'  Order By A.IdRuta, A.NoEconomico"
        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlDataReader
        rdBuscar = cmdBuscar.ExecuteReader
        Dim strFolio As String = "", strSerie As String = ""
        Dim strFecha As String = ""
        Dim strUnidad As String = ""
        Dim acu(999) As Decimal
        Dim strCamion As String = "?"
        Dim strFechaMov As String = ""
        Dim total As Double = 0
        Dim totalu As Double = 0
        Dim totalg As Double = 0
        Dim totalug As Double = 0
        Dim diferencia As Double = 0

        rng = rng + 1
        Do While rdBuscar.Read()
            If strCamion <> rdBuscar("IdRuta").ToString.Trim Then
                If strCamion <> "?" Then
                    wb.SHEETS(1).cells(rng, 1).value = "Totales"
                    wb.SHEETS(1).cells(rng, 2).value = totalu
                    wb.SHEETS(1).cells(rng, 3).value = total
                    wb.SHEETS(1).cells(rng, 4).value = total / totalu

                    total = 0
                    totalu = 0
                    rng = rng + 2 ' Separa entre ruta
                End If
                wb.SHEETS(1).cells(rng, 1).value = "Ruta: " & rdBuscar("Nombre").ToString.Trim
                wb.SHEETS(1).cells(rng, 1).font.size = 12
                wb.SHEETS(1).cells(rng, 1).font.bold = -1
                rng = rng + 1
                wb.SHEETS(1).cells(rng, 1).value = "Autobus"
                wb.SHEETS(1).cells(rng, 2).value = "Litros"
                wb.SHEETS(1).cells(rng, 3).value = "Kilometros"
                wb.SHEETS(1).cells(rng, 4).value = "Rendimiento"
                wb.SHEETS(1).cells(rng, 5).value = "Standar"
                wb.SHEETS(1).cells(rng, 6).value = "Diferencia"

                For i = 1 To 6
                    wb.SHEETS(1).cells(rng, i).font.bold = True
                Next
                rng = rng + 1
            End If
            strCamion = rdBuscar("IdRuta").ToString.Trim

            wb.SHEETS(1).cells(rng, 1).value = rdBuscar("NoEconomico")
            wb.SHEETS(1).cells(rng, 2).value = rdBuscar("Litros")
            wb.SHEETS(1).cells(rng, 3).value = rdBuscar("KilometrosTotales")
            wb.SHEETS(1).cells(rng, 4).value = rdBuscar("KilometrosTotales") / rdBuscar("Litros")
            wb.SHEETS(1).cells(rng, 5).value = rdBuscar("RendimientoStandart")
            diferencia = wb.SHEETS(1).cells(rng, 4).value - wb.SHEETS(1).cells(rng, 5).value
            wb.SHEETS(1).cells(rng, 6).value = diferencia
            If chColores.Checked = True Then
                If diferencia < 0 Then
                    For i = 1 To 6
                        wb.SHEETS(1).cells(rng, i).interior.COLORINDEX = 3 'Rojo
                        wb.SHEETS(1).cells(rng, i).font.COLORINDEX = 2
                        wb.SHEETS(1).cells(rng, i).FONT.BOLD = True
                    Next
                ElseIf diferencia > 0.009 Then
                    For i = 1 To 6
                        wb.SHEETS(1).cells(rng, i).interior.COLORINDEX = 5 'Azul
                        wb.SHEETS(1).cells(rng, i).font.COLORINDEX = 2
                        wb.SHEETS(1).cells(rng, i).FONT.BOLD = True
                    Next
                Else
                    For i = 1 To 6
                        wb.SHEETS(1).cells(rng, i).interior.COLORINDEX = 4 'Verde
                        wb.SHEETS(1).cells(rng, i).font.COLORINDEX = 2
                        wb.SHEETS(1).cells(rng, i).FONT.BOLD = True
                    Next
                End If
            End If

            totalu = totalu + wb.SHEETS(1).cells(rng, 2).value
            total = total + wb.SHEETS(1).cells(rng, 3).value
            totalug = totalug + wb.SHEETS(1).cells(rng, 2).value
            totalg = totalg + wb.SHEETS(1).cells(rng, 3).value

            rng = rng + 1
            'End If
        Loop
        wb.SHEETS(1).cells(rng, 1).value = "Totales"
        wb.SHEETS(1).cells(rng, 2).value = totalu
        wb.SHEETS(1).cells(rng, 3).value = total
        Try
            wb.SHEETS(1).cells(rng, 4).value = total / totalu
        Catch ex As Exception

        End Try


        rng = rng + 2

        wb.SHEETS(1).cells(rng, 1).value = "Total General"
        wb.SHEETS(1).cells(rng, 2).value = totalug
        wb.SHEETS(1).cells(rng, 3).value = totalg
        If chColores.Checked = True Then
            rng = rng + 2
            wb.SHEETS(1).cells(rng, 1).interior.COLORINDEX = 3 'Rojo
            wb.SHEETS(1).cells(rng, 2).value = "Debajo del standar"
            wb.SHEETS(1).cells(rng + 1, 1).interior.COLORINDEX = 4 'Verde
            wb.SHEETS(1).cells(rng + 1, 2).value = "Dentro del standar"
            wb.SHEETS(1).cells(rng + 2, 1).interior.COLORINDEX = 5 'Azul
            wb.SHEETS(1).cells(rng + 2, 2).value = "Arriba del standar"
        End If


        wb.SHEETS(1).columns(1).columnwidth = 13
        wb.SHEETS(1).columns(2).columnwidth = 12
        wb.SHEETS(1).columns(3).columnwidth = 12
        wb.SHEETS(1).columns(4).columnwidth = 12
        wb.SHEETS(1).columns(5).columnwidth = 12
        wb.SHEETS(1).columns(6).columnwidth = 12
        wb.SHEETS(1).columns(2).NumberFormat = "#,##0.00"
        wb.SHEETS(1).columns(3).NumberFormat = "#,##0.00"
        wb.SHEETS(1).columns(4).NumberFormat = "#,##0.00"
        wb.SHEETS(1).columns(5).NumberFormat = "#,##0.00"
        wb.SHEETS(1).columns(6).NumberFormat = "#,##0.00"


        wb.SHEETS(1).cells(1, 1).value = EmpresaActiva
        wb.SHEETS(1).cells(1, 1).FONT.SIZE = 14
        wb.SHEETS(1).cells(2, 1).value = "Rendimientos de Diesel del " & Me.datFecha1.Value.ToString("d") & " al " & Me.datFecha2.Value.ToString("d")
        wb.SHEETS(1).cells(2, 1).FONT.SIZE = 10
        wb.SHEETS(1).cells(3, 1).value = "Impreso el " & Now().Day & "-" & Now().Month & "-" & Now().Year
        wb.SHEETS(1).cells(3, 1).FONT.SIZE = 8
    End Sub
    Sub autobus()
        Dim xl As Object
        Dim wb As Object
        Dim i As Integer
        xl = CreateObject("Excel.Application")
        xl.Visible = True
        xl.Workbooks.Add()
        wb = xl.activeworkbook
        Dim rng As Integer
        rng = 5

        wb.SHEETS(1).cells(1, 1).value = "Imprimiendo..."
        wb.SHEETS(1).columns(1).columnwidth = 15

        Dim strFecha1 As String = datFecha1.Value.ToString("MM/dd/yyyy")
        Dim strFecha2 As String = datFecha2.Value.ToString("MM/dd/yyyy")
        '--------
        Dim cnconn As New SqlConnection
        cnconn.ConnectionString = CitraConnection
        Dim strSql As String = ""
        cnconn.Open()
        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnconn
        Dim cmdAsignar As New SqlCommand
        cmdAsignar.Connection = cnconn

        Dim t As Integer = lstA.Items.Count
        Dim strBUSES As String = "'"

        For i = 0 To t - 2
            strBUSES = strBUSES & Me.lstA.Items(i).ToString.Trim & "','"
        Next

        strBUSES = strBUSES & Me.lstA.Items(t - 1).ToString.Trim & "'"

        strSql = "SELECT IdRuta,NombreRuta,IdAutobus,NoEconomico,Litros,KilometrosTotales,RendimientoStandart FROM Diesel WHERE CONVERT(VARCHAR,Fecha,101)>='" & strFecha1 & "' AND CONVERT(VARCHAR,Fecha,101)<='" & strFecha2 & "' AND NoEconomico IN (" & strBUSES & ") Order By IdRuta, NoEconomico"


        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlDataReader
        rdBuscar = cmdBuscar.ExecuteReader
        Dim strFolio As String = "", strSerie As String = ""
        Dim strFecha As String = ""
        Dim strUnidad As String = ""
        Dim acu(999) As Decimal
        Dim strCamion As String = "?"
        Dim strFechaMov As String = ""
        Dim total As Double = 0
        Dim totalu As Double = 0
        Dim totalg As Double = 0
        Dim totalug As Double = 0
        Dim diferencia As Double = 0


        rng = rng + 1
        Do While rdBuscar.Read()
            If strCamion <> rdBuscar("IdRuta").ToString.Trim Then
                If strCamion <> "?" Then
                    wb.SHEETS(1).cells(rng, 1).value = "Totales"
                    wb.SHEETS(1).cells(rng, 2).value = totalu
                    wb.SHEETS(1).cells(rng, 3).value = total
                    wb.SHEETS(1).cells(rng, 4).value = total / totalu

                    total = 0
                    totalu = 0
                    rng = rng + 2 ' Separa entre ruta
                End If
                wb.SHEETS(1).cells(rng, 1).value = "Ruta: " & rdBuscar("NombreRuta").ToString.Trim
                wb.SHEETS(1).cells(rng, 1).font.size = 12
                wb.SHEETS(1).cells(rng, 1).font.bold = -1
                rng = rng + 1
                wb.SHEETS(1).cells(rng, 1).value = "Autobus"
                wb.SHEETS(1).cells(rng, 2).value = "Litros"
                wb.SHEETS(1).cells(rng, 3).value = "Kilometros"
                wb.SHEETS(1).cells(rng, 4).value = "Rendimiento"
                wb.SHEETS(1).cells(rng, 5).value = "Standar"
                wb.SHEETS(1).cells(rng, 6).value = "Diferencia"

                For i = 1 To 6
                    wb.SHEETS(1).cells(rng, i).font.bold = True
                Next
                rng = rng + 1
            End If
            strCamion = rdBuscar("IdRuta").ToString.Trim

            wb.SHEETS(1).cells(rng, 1).value = rdBuscar("NoEconomico")
            wb.SHEETS(1).cells(rng, 2).value = rdBuscar("Litros")
            wb.SHEETS(1).cells(rng, 3).value = rdBuscar("KilometrosTotales")
            wb.SHEETS(1).cells(rng, 4).value = rdBuscar("KilometrosTotales") / rdBuscar("Litros")
            wb.SHEETS(1).cells(rng, 5).value = rdBuscar("RendimientoStandart")
            diferencia = wb.SHEETS(1).cells(rng, 4).value - wb.SHEETS(1).cells(rng, 5).value
            wb.SHEETS(1).cells(rng, 6).value = diferencia
            If chColores.Checked = True Then
                If diferencia < 0 Then
                    For i = 1 To 6
                        wb.SHEETS(1).cells(rng, i).interior.COLORINDEX = 3 'Rojo
                        wb.SHEETS(1).cells(rng, i).font.COLORINDEX = 2
                        wb.SHEETS(1).cells(rng, i).FONT.BOLD = True
                    Next
                ElseIf diferencia > 0.009 Then
                    For i = 1 To 6
                        wb.SHEETS(1).cells(rng, i).interior.COLORINDEX = 5 'Azul
                        wb.SHEETS(1).cells(rng, i).font.COLORINDEX = 2
                        wb.SHEETS(1).cells(rng, i).FONT.BOLD = True
                    Next
                Else
                    For i = 1 To 6
                        wb.SHEETS(1).cells(rng, i).interior.COLORINDEX = 4 'Verde
                        wb.SHEETS(1).cells(rng, i).font.COLORINDEX = 2
                        wb.SHEETS(1).cells(rng, i).FONT.BOLD = True
                    Next
                End If
            End If

            totalu = totalu + wb.SHEETS(1).cells(rng, 2).value
            total = total + wb.SHEETS(1).cells(rng, 3).value
            totalug = totalug + wb.SHEETS(1).cells(rng, 2).value
            totalg = totalg + wb.SHEETS(1).cells(rng, 3).value

            rng = rng + 1
            'End If
        Loop
        wb.SHEETS(1).cells(rng, 1).value = "Totales"
        wb.SHEETS(1).cells(rng, 2).value = totalu
        wb.SHEETS(1).cells(rng, 3).value = total
        wb.SHEETS(1).cells(rng, 4).value = total / totalu

        rng = rng + 2

        wb.SHEETS(1).cells(rng, 1).value = "Total General"
        wb.SHEETS(1).cells(rng, 2).value = totalug
        wb.SHEETS(1).cells(rng, 3).value = totalg

        If chColores.Checked = True Then
            rng = rng + 2
            wb.SHEETS(1).cells(rng, 1).interior.COLORINDEX = 3 'Rojo
            wb.SHEETS(1).cells(rng, 2).value = "Debajo del standar"
            wb.SHEETS(1).cells(rng + 1, 1).interior.COLORINDEX = 4 'Verde
            wb.SHEETS(1).cells(rng + 1, 2).value = "Dentro del standar"
            wb.SHEETS(1).cells(rng + 2, 1).interior.COLORINDEX = 5 'Azul
            wb.SHEETS(1).cells(rng + 2, 2).value = "Arriba del standar"
        End If


        wb.SHEETS(1).columns(1).columnwidth = 13
        wb.SHEETS(1).columns(2).columnwidth = 12
        wb.SHEETS(1).columns(3).columnwidth = 12
        wb.SHEETS(1).columns(4).columnwidth = 12
        wb.SHEETS(1).columns(5).columnwidth = 12
        wb.SHEETS(1).columns(6).columnwidth = 12
        wb.SHEETS(1).columns(2).NumberFormat = "#,##0.00"
        wb.SHEETS(1).columns(3).NumberFormat = "#,##0.00"
        wb.SHEETS(1).columns(4).NumberFormat = "#,##0.00"
        wb.SHEETS(1).columns(5).NumberFormat = "#,##0.00"
        wb.SHEETS(1).columns(6).NumberFormat = "#,##0.00"



        wb.SHEETS(1).cells(1, 1).value = "Autobuses Urbanos Y SubUrbanos de Leòn, S.A. de C.V."
        wb.SHEETS(1).cells(1, 1).FONT.SIZE = 14
        wb.SHEETS(1).cells(2, 1).value = "Rendimientos de Diesel del " & Me.datFecha1.Value.ToString("d") & " al " & Me.datFecha2.Value.ToString("d")
        wb.SHEETS(1).cells(2, 1).FONT.SIZE = 10
        wb.SHEETS(1).cells(3, 1).value = "Impreso el " & Now().Day & "-" & Now().Month & "-" & Now().Year
        wb.SHEETS(1).cells(3, 1).FONT.SIZE = 8

    End Sub
    Sub ruta()
        Dim xl As Object
        Dim wb As Object
        Dim i As Integer
        xl = CreateObject("Excel.Application")
        xl.Visible = True
        xl.Workbooks.Add()
        wb = xl.activeworkbook
        Dim rng As Integer
        rng = 5

        wb.SHEETS(1).cells(1, 1).value = "Imprimiendo..."
        wb.SHEETS(1).columns(1).columnwidth = 15

        Dim strFecha1 As String = datFecha1.Value.ToString("MM/dd/yyyy")
        Dim strFecha2 As String = datFecha2.Value.ToString("MM/dd/yyyy")
        '--------
        Dim cnconn As New SqlConnection
        cnconn.ConnectionString = CitraConnection
        Dim strSql As String = ""
        cnconn.Open()
        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnconn
        Dim cmdAsignar As New SqlCommand
        cmdAsignar.Connection = cnconn
        Dim clasifi As Integer
        Dim t As Integer = lstR.Items.Count
        Dim strRutas As String = ""

        For i = 0 To t - 2
            clasifi = BuscarIntDatoCitra("Rutas", "IdRuta", "Nombre='" & Me.lstR.Items(i).ToString.Trim & "'")
            strRutas = strRutas & clasifi & ","
        Next
        clasifi = BuscarIntDatoCitra("Rutas", "IdRuta", "Nombre='" & Me.lstR.Items(t - 1).ToString.Trim & "'")
        strRutas = strRutas & clasifi


        strSql = "SELECT IdRuta,NombreRuta,IdAutobus,NoEconomico,Litros,KilometrosTotales,RendimientoStandart FROM Diesel WHERE CONVERT(VARCHAR,Fecha,101)>='" & strFecha1 & "' AND CONVERT(VARCHAR,Fecha,101)<='" & strFecha2 & "' AND IdRuta in (" & strRutas & ")  Order By IdRuta, NoEconomico"

        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlDataReader
        rdBuscar = cmdBuscar.ExecuteReader
        Dim strFolio As String = "", strSerie As String = ""
        Dim strFecha As String = ""
        Dim strUnidad As String = ""
        Dim acu(999) As Decimal
        Dim strCamion As String = "?"
        Dim strFechaMov As String = ""
        Dim total As Double = 0
        Dim totalu As Double = 0
        Dim totalg As Double = 0
        Dim totalug As Double = 0
        Dim diferencia As Double = 0


        rng = rng + 1
        Do While rdBuscar.Read()
             If strCamion <> rdBuscar("IdRuta").ToString.Trim Then
                If strCamion <> "?" Then
                    wb.SHEETS(1).cells(rng, 1).value = "Totales"
                    wb.SHEETS(1).cells(rng, 2).value = totalu
                    wb.SHEETS(1).cells(rng, 3).value = total
                    wb.SHEETS(1).cells(rng, 4).value = total / totalu

                    total = 0
                    totalu = 0
                    rng = rng + 2 ' Separa entre ruta
                End If
                wb.SHEETS(1).cells(rng, 1).value = "Ruta: " & rdBuscar("NombreRuta").ToString.Trim
                wb.SHEETS(1).cells(rng, 1).font.size = 12
                wb.SHEETS(1).cells(rng, 1).font.bold = -1
                rng = rng + 1
                wb.SHEETS(1).cells(rng, 1).value = "Autobus"
                wb.SHEETS(1).cells(rng, 2).value = "Litros"
                wb.SHEETS(1).cells(rng, 3).value = "Kilometros"
                wb.SHEETS(1).cells(rng, 4).value = "Rendimiento"
                wb.SHEETS(1).cells(rng, 5).value = "Standar"
                wb.SHEETS(1).cells(rng, 6).value = "Diferencia"

                For i = 1 To 6
                    wb.SHEETS(1).cells(rng, i).font.bold = True
                Next
                rng = rng + 1
            End If
            strCamion = rdBuscar("IdRuta").ToString.Trim

            wb.SHEETS(1).cells(rng, 1).value = rdBuscar("NoEconomico")
            wb.SHEETS(1).cells(rng, 2).value = rdBuscar("Litros")
            wb.SHEETS(1).cells(rng, 3).value = rdBuscar("KilometrosTotales")
            wb.SHEETS(1).cells(rng, 4).value = rdBuscar("KilometrosTotales") / rdBuscar("Litros")
            wb.SHEETS(1).cells(rng, 5).value = rdBuscar("RendimientoStandart")
            diferencia = wb.SHEETS(1).cells(rng, 4).value - wb.SHEETS(1).cells(rng, 5).value
            wb.SHEETS(1).cells(rng, 6).value = diferencia
            If chColores.Checked = True Then
                If diferencia < 0 Then
                    For i = 1 To 6
                        wb.SHEETS(1).cells(rng, i).interior.COLORINDEX = 3 'Rojo
                        wb.SHEETS(1).cells(rng, i).font.COLORINDEX = 2
                        wb.SHEETS(1).cells(rng, i).FONT.BOLD = True
                    Next
                ElseIf diferencia > 0.009 Then
                    For i = 1 To 6
                        wb.SHEETS(1).cells(rng, i).interior.COLORINDEX = 5 'Azul
                        wb.SHEETS(1).cells(rng, i).font.COLORINDEX = 2
                        wb.SHEETS(1).cells(rng, i).FONT.BOLD = True
                    Next
                Else
                    For i = 1 To 6
                        wb.SHEETS(1).cells(rng, i).interior.COLORINDEX = 4 'Verde
                        wb.SHEETS(1).cells(rng, i).font.COLORINDEX = 2
                        wb.SHEETS(1).cells(rng, i).FONT.BOLD = True
                    Next
                End If
            End If


            totalu = totalu + wb.SHEETS(1).cells(rng, 2).value
            total = total + wb.SHEETS(1).cells(rng, 3).value
            totalug = totalug + wb.SHEETS(1).cells(rng, 2).value
            totalg = totalg + wb.SHEETS(1).cells(rng, 3).value

            rng = rng + 1
            'End If
        Loop
        wb.SHEETS(1).cells(rng, 1).value = "Totales"
        wb.SHEETS(1).cells(rng, 2).value = totalu
        wb.SHEETS(1).cells(rng, 3).value = total
        wb.SHEETS(1).cells(rng, 4).value = total / totalu

        rng = rng + 2

        wb.SHEETS(1).cells(rng, 1).value = "Total General"
        wb.SHEETS(1).cells(rng, 2).value = totalug
        wb.SHEETS(1).cells(rng, 3).value = totalg

        If chColores.Checked = True Then
            rng = rng + 2
            wb.SHEETS(1).cells(rng, 1).interior.COLORINDEX = 3 'Rojo
            wb.SHEETS(1).cells(rng, 2).value = "Debajo del standar"
            wb.SHEETS(1).cells(rng + 1, 1).interior.COLORINDEX = 4 'Verde
            wb.SHEETS(1).cells(rng + 1, 2).value = "Dentro del standar"
            wb.SHEETS(1).cells(rng + 2, 1).interior.COLORINDEX = 5 'Azul
            wb.SHEETS(1).cells(rng + 2, 2).value = "Arriba del standar"
        End If


        wb.SHEETS(1).columns(1).columnwidth = 13
        wb.SHEETS(1).columns(2).columnwidth = 12
        wb.SHEETS(1).columns(3).columnwidth = 12
        wb.SHEETS(1).columns(4).columnwidth = 12
        wb.SHEETS(1).columns(5).columnwidth = 12
        wb.SHEETS(1).columns(6).columnwidth = 12
        wb.SHEETS(1).columns(2).NumberFormat = "#,##0.00"
        wb.SHEETS(1).columns(3).NumberFormat = "#,##0.00"
        wb.SHEETS(1).columns(4).NumberFormat = "#,##0.00"
        wb.SHEETS(1).columns(5).NumberFormat = "#,##0.00"
        wb.SHEETS(1).columns(6).NumberFormat = "#,##0.00"



        wb.SHEETS(1).cells(1, 1).value = "Autobuses Urbanos Y SubUrbanos de Leòn, S.A. de C.V."
        wb.SHEETS(1).cells(1, 1).FONT.SIZE = 14
        wb.SHEETS(1).cells(2, 1).value = "Rendimientos de Diesel del " & Me.datFecha1.Value.ToString("d") & " al " & Me.datFecha2.Value.ToString("d")
        wb.SHEETS(1).cells(2, 1).FONT.SIZE = 10
        wb.SHEETS(1).cells(3, 1).value = "Impreso el " & Now().Day & "-" & Now().Month & "-" & Now().Year
        wb.SHEETS(1).cells(3, 1).FONT.SIZE = 8
    End Sub

    Private Sub chColores_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles chColores.CheckedChanged
        If chColores.Checked = True Then
            Panel1.BackColor = Color.Blue
            Panel2.BackColor = Color.Green
            Panel3.BackColor = Color.Red
        Else
            Panel1.BackColor = Color.Gray
            Panel2.BackColor = Color.Gray
            Panel3.BackColor = Color.Gray

        End If
    End Sub

    Private Sub btnAbrir_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

    End Sub

    Private Sub RadButton2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Close()

    End Sub

    Private Sub ToolStripButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton1.Click
        'Registra actividad
        Bitacora.RegistraActividad("Imprimió el reporte de rendimientos de diesel del dia " & datFecha1.Text & " al " & datFecha2.Text)

        If RadioButton1.Checked = True Then
            todos()
        ElseIf RadioButton2.Checked = True Then
            ruta()
        Else
            autobus()
        End If
        'Dim t As Integer = lstR.Items.Count
        'MsgBox(t)
        'Dim i As Integer
        'For i = 0 To lstR.Items.Count - 1
        'MsgBox(lstR.Items(i).ToString)
        'Next
    End Sub

    Private Sub ToolStripButton2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton2.Click
        Close()
    End Sub
End Class